/*@Author: Duarte José Santos Peres - 5621
 *@Course: Engenharia Informática - Diurno
 *@Discipline: Liguagens de Programação - CSharp
 *@Version: 03/07/2011
 */

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Mono.Data.SqliteClient;
using System.IO;

namespace Pristine
{
	class MainClass
	{

        //####################################################################################################################################
		/* POR ANO
		 * Recebe como pârametro o ano para completar a query e o nome do ficheiro onde vai gravar. 
		 * Numero de Docentes por ano
		 */
        static void Peryear(int year, string namefile){
            //Inicializar uma lista do tipo string
            List<string> number = new List<string>();
            //string guarda o caminho da base de dados
            string connectionString = "URI=file:pristine.db,version=3";
			//variável dbcon do tipo IDbConnection para realizar a conexão com a base de dados utilizando a string que possui o caminho da base de dados
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
			//Abre a base de dados
            dbcon.Open();
			//variável dbcmd do tipo IDbCommand para executar as query's
            IDbCommand dbcmd = dbcon.CreateCommand();
			//string que guarda a query
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + year;
            dbcmd.CommandText = sql;
			//query executada a partir da variável dbcmd e guardada na variável reader
            IDataReader reader = dbcmd.ExecuteReader();
			//Inicialização da variável que vai permitir escrever num ficheiro
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
			//Enquanto o variável reader tiver elementos
            while(reader.Read())
            {
				//a string recebe o elemento 
                string n = reader.GetString(0);
				//adicionar o elemento na lista
                number.Add(n);
            }
			//depois de adicionar todos os elementos realiza-se o SORT um dos requisitos da trabalho
            number.Sort();
			//escreve no ficheiro o tipo de sistema operativo que está a ser executado
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
			//ciclo atraves da lista onde a string recebe os valores
            foreach (string num in number)
            {
				//escreve na janela da consola
                Console.WriteLine("Total number of teachers in the higher education system in the year 200{0} - {1}", year, num);
                //escreve no ficheiro
                sw.WriteLine("Total number of teachers in the higher education system in the year 200{0} - {1}", year, num);
            }
            //Fecha o ficheir
            sw.Close();
            //Limpa e fecha as variáveis
            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        //####################################################################################################################################



        //####################################################################################################################################
		/* POR ESTABELECIMENTO E POR ANO
		 * Recebe como pârametro o ano e o estabelecimento para completar a query e o nome do ficheiro onde vai gravar. 
		 * Numero de Docentes por ano e por estabelecimento
		 */
        static void PerEstablishmentPeryear(int year, string establishment, string namefile)
        {
            List<string> number = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + year +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '"+ establishment +"' )";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            while (reader.Read())
            {
                string n = reader.GetString(0);
                number.Add(n);
            }
            number.Sort();
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            foreach (string num in number) // Loop through List with foreach
            {
                Console.WriteLine("Total number of teachers in establishment {0} and year 200{1} - {2}", establishment, year, num);
                //Write a line of text
                sw.WriteLine("Total number of teachers in establishment {0} and year 200{1} - {2}", establishment, year, num);
            }
            //Close the file
            sw.Close();
            // clean up
            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        //####################################################################################################################################




        //####################################################################################################################################
        /* POR GRAU E POR ANO
		 * Recebe como pârametro o ano e o grau para completar a query e o nome do ficheiro onde vai gravar. 
		 * Numero de Docentes por ano e por grau
		 */
        static void PerDegreePeryear(int year, string degree, string namefile)
        {
            List<string> number = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + year +
                         " AND grau_id= (SELECT id FROM informatica_grau WHERE designacao= '" + degree + "' )";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            while (reader.Read())
            {
                string n = reader.GetString(0);
                number.Add(n);
            }
            number.Sort();
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            foreach (string num in number) // Loop through List with foreach
            {
                Console.WriteLine("Total number of teachers in degree {0} and year 200{1} - {2}", degree, year, num);
                //Write a line of text
                sw.WriteLine("Total number of teachers in degree {0} and year 200{1} - {2}", degree, year, num);
            }
            //Close the file
            sw.Close();
            // clean up
            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        //####################################################################################################################################



        //####################################################################################################################################
 		/* POR ESTABELECIMENTO, POR GRAU E POR ANO
		 * Recebe como pârametro o ano, o grau e o estabelecimento para completar a query e o nome do ficheiro onde vai gravar. 
		 * Numero de Docentes por ano, por grau e por estabelecimento
		 */
        static void PerEstablishmentPerDegreePeryear(int year, string degree, string establishment, string namefile)
        {
            List<string> number = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + year +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '"+ establishment +"' )" +
                         " AND grau_id= (SELECT id FROM informatica_grau WHERE designacao= '" + degree + "' )";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            while (reader.Read())
            {
                string n = reader.GetString(0);
                number.Add(n);
            }
            number.Sort();
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            foreach (string num in number) // Loop through List with foreach
            {
                Console.WriteLine("Total number of teachers in degree {0}, in establishment {1} and year 200{2} - {3}", degree, establishment, year, num);
                //Write a line of text
                sw.WriteLine("Total number of teachers in degree {0}, in establishment {1} and year 200{2} - {3}", degree, establishment, year, num);
            }
            //Close the file
            sw.Close();
            // clean up
            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        //####################################################################################################################################


        //####################################################################################################################################
        /*DOCTORADOS POR ESTABELECIMENTO E POR ANO
		 * Recebe como pârametro o ano e o estabelecimento para completar a query, o grau vai receber o id quando o ciclo de estudo
		 * é igual a 3 e o nome do ficheiro onde vai gravar. 
		 * Numero de Titulares com grau Doutorado por ano e por estabelecimento
		 */
        static void DoctoratePerEstablishmentPeryear(int year, string establishment, string namefile)
        {
            List<string> number = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + year +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + establishment + "' )" +
                         " AND grau_id= (SELECT id FROM informatica_grau WHERE ciclo_estudos_id= 3)";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            while (reader.Read())
            {
                string n = reader.GetString(0);
                number.Add(n);
            }
            number.Sort();
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            foreach (string num in number) // Loop through List with foreach
            {
                Console.WriteLine("The number of holders of a doctorate degree, in establishment {0} and year 200{1} - {2}", establishment, year, num);
                //Write a line of text
                sw.WriteLine("The number of holders of a doctorate degree, in establishment {0} and year 200{1} - {2}", establishment, year, num);
            }
            //Close the file
            sw.Close();
            // clean up
            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        //####################################################################################################################################



        //####################################################################################################################################
        /*ID DOS DOCENTES COM GRAU DOCTORADOS PARA O SER UTILIZADO NO METODO - ListDoctoratePerEstablishmentPeryear
		 * Recebe como pârametro o ano, o estabelecimento para completar a query e a lista que guarda o resutado final, o grau vai receber o id quando o ciclo de estudo
		 * é igual a 3. 
		 * Precisa-se de uma lista com os id's dos docentes com estas característica para completar a query do metodo seguinte
		 */
        static void IDDoctorate(int year, string establishment, List<string> id)
        {
           
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano = " + year +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '"+ establishment +
                         "') AND grau_id= (SELECT id FROM informatica_grau WHERE ciclo_estudos_id= 3)";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
               string n = reader.GetString(0);
               id.Add(n);
            }
        }

        /*LISTA DOCTORADOS POR ESTABELECIMENTO E POR ANO
		 * Recebe como pârametro o ano e o estabelecimento para completar a query e o nome do ficheiro onde vai gravar. 
		 * Lista de Titulares com grau Doutorado por ano e por estabelecimento
		 */
        static void ListDoctoratePerEstablishmentPeryear(int year, string establishment, string namefile)
        {
            
            List<string> list = new List<string>();
            List<string> id = new List<string>();
            IDDoctorate(year, establishment, id);
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            foreach (string complete in id)
            {
                string connectionString = "URI=file:pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();
                string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
 
                while (reader.Read())
                {
                    string n = reader.GetString(0);
                    list.Add(n);
                }

                // clean up
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
            //Depois de ter a list com todos os elementos fazer o sort
            list.Sort();
            sw.WriteLine("The set of holders of a doctorate degree in establishment {0} and year 200{1}: ", establishment, year);
            foreach (string listas in list) // Loop through List with foreach
            {
                Console.WriteLine(listas);
            }
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            sw.WriteLine("The set of holders of a doctorate degree in establishment {0} and year 200{1}: ", establishment, year);
            foreach (string listas in list) // Loop through List with foreach
            {
                sw.WriteLine(listas);
            }
            //Close the file
            sw.Close();
        }
        //####################################################################################################################################




        //####################################################################################################################################
        /*ID DOS DOCENTES NUM ESTABELECIMENTO E POR ANO PARA UTILIZAR NO METODO - ListTeachers
		 * Recebe como pârametro o ano e o estabelecimento para completar a query e a lista que guarda o resutado final. 
		 * Lista de id's dos docentes por ano e por estabelecimento para completar a query do metodo seguinte
		 */
        static void ID(int year, string establishment, List<string> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano = " + year +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + establishment +"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                string n = reader.GetString(0);
                id.Add(n);
            }
        }


        /*LISTA DOCENTES DE UM ESTABELECIMENTO POR ANO PARA UTILIZAR NO METODO - ListTeachersChangeEstablishmentPerYear
		 * Recebe como pârametro o ano e o estabelecimento para completar a query e a lista que guarda o resutado final. 
		 * Lista de docentes por ano e por estabelecimento para comparar no metodo seguinte
		 */
        static void ListTeachers(int year, string establishment, List<string> list)
        {

            List<string> id = new List<string>();
            ID(year, establishment, id);
            foreach (string complete in id)
            {
                string connectionString = "URI=file:pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();
                string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string n = reader.GetString(0);
                    list.Add(n);
                }

                // clean up
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
            //Depois de ter a list com todos os elementos fazer o sort
            list.Sort();
        }

        /*LISTA DE PROFESSORES QUE MUDARAM DE UM ESTABELECIMENTO PARA OUTRO POR ANO
		 * Recebe como pârametro o ano e o nome de dois estabelecimentos onde vai comparar os nomes dos docentes de um estabelecimento e de outro
		 * caso seja igual adiciona à lista e no final apresenta os nomes contidos na lista. 
		 * Lista de Docentes que mudaram de um estabelecimento para um outro por ano
		 */
        static void ListTeachersChangeEstablishmentPerYear(int year, string establishment1, string establishment2, string namefile) {
            List<string> list = new List<string>();
            List<string> list1 = new List<string>();
            List<string> list2 = new List<string>();
            ListTeachers(year, establishment1, list1);
            ListTeachers(year, establishment2, list2);

            foreach (string teachers1 in list1)
            {
                foreach (string teachers2 in list2)
                {
                    if (teachers1 == teachers2)
                    {
                        list.Add(teachers1);
                    }else{
						continue;
					}
                }
            }
            list.Sort();
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            Console.WriteLine("The set of teachers that changed from establishment {0} to establishment {1} in year {2}: ", establishment1,establishment2, year);
            foreach (string listas in list) // Loop through List with foreach
            {
                Console.WriteLine(listas);
            }
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            sw.WriteLine("The set of teachers that changed from establishment {0} to establishment {1} in year {2}: ", establishment1, establishment2, year);
            foreach (string listas in list) // Loop through List with foreach
            {
                sw.WriteLine(listas);
            }
            //Close the file
            sw.Close();
        }
        //####################################################################################################################################





        //####################################################################################################################################
        /*LISTA DE ESTABELECIMENTO POR ANO
		 * Recebe como pârametro o ano e o nome do ficheiro onde é gravado os resultados
		 * Lista de estabelecimentos por ano
		 */
        static void ListEstablismentPerYear(int year, string namefile)
        {

            List<string> list = new List<string>();
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT designacao FROM informatica_estabelecimento WHERE ano =" + year;
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
               string n = reader.GetString(0);
               list.Add(n);
            }

                // clean up
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            
            //Depois de ter a list com todos os elementos fazer o sort
            list.Sort();
            Console.WriteLine("List of establishments in year 200{0}: ", year);
            foreach (string listas in list) // Loop through List with foreach
            {
                Console.WriteLine(listas);
            }
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            sw.WriteLine("List of establishments in year 200{0}: ", year);
            foreach (string listas in list) // Loop through List with foreach
            {
                sw.WriteLine(listas);
            }
            //Close the file
            sw.Close();

        }
        //####################################################################################################################################




        //####################################################################################################################################
        /*ID DOS DOCENTES DE UM GRAU POR ANO PARA UTILIZAR NO METODO - ListHoldersDegreePerYear
		 * Recebe como pârametro o ano, o grau e a lista onde é gravado os resultados
		 * Lista de id's dos docentes por ano e por grau
		 */
        static void IDDocentesGrauPorAno(int year,string degree, List<string> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            //ciclo_estudos_id = 2 - todos os mestrados
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano = " + year +
                         " AND grau_id= (SELECT id FROM informatica_grau WHERE designacao='"+degree+"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                string n = reader.GetString(0);
                id.Add(n);
            }
        }


        /*LISTA DE TITULARES DE UM GRAU POR ANO
		 * Recebe como pârametro o ano, o grau e o nome do ficheiro onde é gravado os resultados
		 * Lista de titulares por ano e por grau
		 */
        static void ListHoldersDegreePerYear(int year, string degree,string namefile)
        {


            List<string> list = new List<string>();
            List<string> id = new List<string>();
            IDDocentesGrauPorAno(year,degree,id);
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            foreach (string complete in id)
            {
                string connectionString = "URI=file:pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();
                string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string n = reader.GetString(0);
                    list.Add(n);
                }

                // clean up
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
            //Depois de ter a list com todos os elementos fazer o sort
            list.Sort();
            Console.WriteLine("List of holders of Masters degree in year {0}: ", year);
            foreach (string listas in list) // Loop through List with foreach
            {
                Console.WriteLine(listas);
            }
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            sw.WriteLine("List of holders of Masters degree in year {0}: ", year);
            foreach (string listas in list) // Loop through List with foreach
            {
                sw.WriteLine(listas);
            }
            //Close the file
            sw.Close();

        }

        //####################################################################################################################################




        //####################################################################################################################################

        /*TOTAL DE DOCENTES DE UM ESTABELECIMENTO E ANO PARA UTILIZAR NO METODO - IDDocentesLeaveOrEntry
		 * Recebe como pârametro o ano, o estabelecimento e a lista onde é gravado os resultados
		 * Lista total de docentes por ano e por estabelecimento
		 */
        static void TotalDocentes(int year, string establishment, List<int> nr)
        {
            
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + year +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + establishment +"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
               int numero = reader.GetInt32(0);
               nr.Add(numero);
            }
        }
        /*ID's DE DOCENTES DE UM ESTABELECIMENTO E ANO PARA UTILIZAR NO METODO - ListPersonnelLeaveOrEntry
		 * Recebe como pârametro o ano, o estabelecimento e a lista onde é gravado os resultados
		 * Lista de id's dos docentes por ano e por estabelecimento
		 */
        static void IDDocentesLeaveOrEntry(int year, string establishment, List<string> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano = " + year +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + establishment +"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
               string nr = reader.GetString(0);
               id.Add(nr);
            }
        }


        /*LISTA DE PESSOAL QUE ABANDONOU INSTITUICAO OU PESSOAL NOVO NA INSTITUICAO
		 * Recebe como pârametro o ano, o estabelecimento e o nome de dois ficheiros para onde guardamos os resultados do pessoal que abandona e
		 * outro do pessoal que entra num instituição
		 * Recebe os id's dos docentes para completar a query e recebe o total de docentes para saber a diferença entre o ano actual e o ano seguinte.
		 * Se a diferença for positiva grava no ficheiro do pessoal que abandona e se a diferença for negativa grava no ficheiro do pessoal novo na instituição.
		 */
        static void ListPersonnelLeaveOrEntry(int year, string establishment ,string namefileLeave , string namefileEntry)
        {
            List<int> actuallist = new List<int>();
            List<int> nextlist = new List<int>();
            List<string> list = new List<string>();
            List<string> id = new List<string>();
            IDDocentesLeaveOrEntry(year, establishment, id);
            StreamWriter swEntry = new StreamWriter(namefileEntry, true, Encoding.UTF8);
            StreamWriter swLeave = new StreamWriter(namefileLeave, true, Encoding.UTF8);
            foreach (string complete in id)
            {
               string connectionString = "URI=file:pristine.db,version=3";
               IDbConnection dbcon;
               dbcon = (IDbConnection)new SqliteConnection(connectionString);
               dbcon.Open();
               IDbCommand dbcmd = dbcon.CreateCommand();
               string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
               dbcmd.CommandText = sql;
               IDataReader reader = dbcmd.ExecuteReader();

               while (reader.Read())
               {
                 string n = reader.GetString(0);
                 list.Add(n);
               }
               // clean up
               reader.Close();
               reader = null;
               dbcmd.Dispose();
               dbcmd = null;
               dbcon.Close();
               dbcon = null;
                
            }
            list.Sort();
            int diference;
            int total1;
            int total2;

            TotalDocentes(year, establishment, actuallist);
            TotalDocentes((year+1), establishment, nextlist);
            foreach (int actual in actuallist)
            {
                total1 = actual;
                        
              foreach (int next in nextlist)
              {
                total2 = next;         
                diference = total1 - total2;
                if (diference < 0)
                {
                    Console.WriteLine("List of new personnel in the institution {0} in year 200{1}: ", establishment, year);
                    foreach (string listas in list) // Loop through List with foreach
                    {
                        Console.WriteLine(listas);   
                    }
                    swEntry.WriteLine("Operating System: " + Environment.OSVersion.ToString());
                    swEntry.WriteLine("List of new personnel in the institution {0} in year 200{1}: ", establishment, year); ;
                    foreach (string listas in list) // Loop through List with foreach
                    {
                       swEntry.WriteLine(listas);
                    }
                }
                else if (diference > 0)
                {
                    Console.WriteLine("List of the personnel leaving the institution {0} in year 200{1} to year 200{2}: ", establishment, year, year + 1);
                    foreach (string listas in list) // Loop through List with foreach
                    {
                        Console.WriteLine(listas);
                    }
                    swLeave.WriteLine("Operating System: " + Environment.OSVersion.ToString());
                    swLeave.WriteLine("List of the personnel leaving the institution {0} in year 200{1} to year 200{2}: ", establishment, year, year + 1); ;
                    foreach (string listas in list) // Loop through List with foreach
                    {
                        swLeave.WriteLine(listas);
                    }

                }
                else{
                    Console.WriteLine("Without alteration the institution {0} in year 200{1} to year 200{2}", establishment, year, year + 1);
                }//Close the file
                swEntry.Close();
                swLeave.Close();
               }
            }
        }
        //####################################################################################################################################






        //####################################################################################################################################

        /* ID DOS DOCENTES NUM ESTABELECIMENTO PARA UTILIZAR NO METODO - ListTeachersToCompare
		 * Recebe como pârametro o estabelecimento e a list que guarda os resultados finais
		 * Lista de id's dos docentes por estabelecimento para completar a query do metodo seguinte
		 */
        static void IDNrDocentes(string establishment, List<string> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + establishment + "')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                string n = reader.GetString(0);
                id.Add(n);
            }
        }


        /*LISTA DOCENTES DE UM ESTABELECIMENTO PARA UTILIZAR NO METODO - NumberTeachersMigrateEstablishment
		 * Recebe como pârametro o estabelecimento e a list que guarda os resultados finais
		 * Lista dos docentes por estabelecimento
		 */
        static void ListTeachersToCompare(string establishment, List<string> list)
        {

            List<string> id = new List<string>();
            IDNrDocentes(establishment, id);
            foreach (string complete in id)
            {
                string connectionString = "URI=file:pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();
                string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string n = reader.GetString(0);
                    list.Add(n);
                }

                // clean up
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
            //Depois de ter a list com todos os elementos fazer o sort
            list.Sort();
        }

        /*NUMERO DE PROFESSORES QUE MIGRARAM DE UM ESTABELECIMENTO PARA OUTRO
		 * Recebe como pârametro dois nomes de estabelecimentos e o nome do ficheiro onde guarda os resultados finais
		 * Recebe os nomes dos docentes e compara os nomes em diferentes estabelecimentos, se os nomes forem iguais incrementa
		 * e posteriormente é gravada essa contagem na lista.
		 */
        static void NumberTeachersMigrateEstablishment( string establishment1, string establishment2, string namefile)
        {
            List<int> list = new List<int>();
            List<string> list1 = new List<string>();
            List<string> list2 = new List<string>();
            ListTeachersToCompare(establishment1, list1);
            ListTeachersToCompare(establishment2, list2);
            int i=0;
            foreach (string teachers1 in list1)
            {
                foreach (string teachers2 in list2)
                {
                    if (teachers1 == teachers2)
                    {
                        i++;
                    }else{
						continue;
					}
                }
            }
            list.Add(i);
            StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);

            Console.WriteLine("Number of teachers migrating from establishment {0} to establishment {1}: ", establishment1, establishment2);
            foreach (int listas in list) // Loop through List with foreach
            {
                Console.WriteLine(listas);
            }
            sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
            sw.WriteLine("Number of teachers migrating from establishment {0} to establishment {1}: ", establishment1, establishment2);
            foreach (int listas in list) // Loop through List with foreach
            {
                sw.WriteLine(listas);
            }
            //Close the file
            sw.Close();
        }

        //#########################################################################################################################
		/*ID DOS DOCENTES NUM ANO E UM ESTABELECIMENTO PARA UTILIZAR NO METODO - NumberTeachersPromoteCategoryPerEstablisment
		 * Recebe como pârametro o ano, o estabelecimento e a list que guarda os resultados finais
		 * Lista de id's dos docentes por estabelecimento e por ano
		 */
        static void IDDocente(int year, string establishment, List<int> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT docente_id FROM informatica_registodocencia WHERE ano =" +year+ " AND estabelecimento_id= (select id from informatica_estabelecimento where designacao='"+establishment+"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                int docente = reader.GetInt32(0);
                id.Add(docente);
            }
        }
		/*ID DAS CATEGORIA NUM ANO E UM ESTABELECIMENTO PARA UTILIZAR NO METODO - NumberTeachersPromoteCategoryPerEstablisment
		 * Recebe como pârametro o ano, o estabelecimento e a list que guarda os resultados finais
		 * Lista de id's das categorias por estabelecimento e por ano
		 */
		static void IDCategoria(int year, string establishment, List<int> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT categoria_id FROM informatica_registodocencia WHERE ano =" +year+ " AND estabelecimento_id= (select id from informatica_estabelecimento where designacao='"+establishment+"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
			{
				int categoria = reader.GetInt32(0);
                id.Add(categoria);
            }
        }


		/*NUMERO DE PROFESSORES QUE FORAM PROMOVIDOS PARA PROXIMA CATEGORIA
		 * Recebe como pârametro o ano, o estabelecimento e o nome do ficheiro que guarda os resultados finais
		 * Recebe os id's dos docentes no ano actual e no proximo ano, se os id's dos docentes forem iguais, vai verificar as
		 * categorias. Recebe os id's das categorias no ano atual e no proximo ano, se os id's forem diferentes incrementa e posteriormente
		 * guarda na lista.  
		 */
        static void NumberTeachersPromoteCategoryPerEstablisment(int year, string establishment, string namefile)
        {
                List<int> actualDocente = new List<int>();
			 	List<int> actualCategoria = new List<int>();
			   	List<int> nextDocente = new List<int>();
			 	List<int> nextCategoria = new List<int>();
                List<int> list = new List<int>();
                IDDocente(year, establishment,actualDocente);
				IDDocente(year+1, establishment,nextDocente);
			    IDCategoria(year, establishment,actualCategoria);
				IDCategoria(year+1, establishment,nextCategoria);
				int count =0;
                foreach(int actualDoce in actualDocente)
                {
					foreach(int nextDoce in nextDocente)
					{

						if(actualDoce == nextDoce)
						{
							foreach(int actualCate in actualCategoria)
                			{
								foreach(int nextCate in nextCategoria)
								{
									if(actualCate == nextCate)
									{
										continue;
									}else{
										count++;
									}
								}
							}
						}else{
							continue;
						}
					}
 
				}
				list.Add(count);
				StreamWriter sw = new StreamWriter(namefile, true, Encoding.UTF8);
            	Console.WriteLine("Number of teachers promoted to the next category in the year 200{0} and in establishment {1}:", year, establishment);
            	foreach (int listas in list) // Loop through List with foreach
            	{
                	Console.WriteLine(listas);
            	}
            	sw.WriteLine("Operating System: " + Environment.OSVersion.ToString());
				sw.WriteLine("Number of teachers promoted to the next category in the year 200{0} and in establishment {1}:", year, establishment);
            	foreach (int listas in list) // Loop through List with foreach
            	{
                	sw.WriteLine(listas);
           		}
            	//Close the file
            	sw.Close();

            
        
        }
     
        //#########################################################################################################################

        static void Main(string[] args)
        {
			//#################################### MENU #############################################
			/*Menu criado para o utilizador visualizar o sistema operativo onde esta a executar o programa.
			 *Tendo o utilizador a possibilidade de escolher as estatísticas ou as listas que quer realizar, introduzindo
			 *os valores pretendidos.
			 */
            Console.WriteLine(">>>> Operating System: " + Environment.OSVersion.ToString() +" <<<<");
            Console.WriteLine(
                        "******************************* WELCOME ***********************************\n"+
                        "                   ___________ Statistics ____________                     \n"+
                        "                                                                           \n" +
                        "1 - Total number of teachers in the higher education system per year       \n" +
                        "2 - Total number of teachers per establishment and per year                \n" +
                        "3 - Total number of teachers per degree and per year                       \n" +
                        "4 - Total number of teachers per degree, per establishment and per year    \n" +
                        "5 - The number of holders of a doctorate degree(3) per establishment and per year \n" +
                        "6 - Number of teachers migrating from one establishment to another         \n" +
                        "7 - Number of teachers promoted to the next category each year per establishment  \n" +
                        "                                                                           \n" +
                        "                   _____________ Lists ____________                        \n" +
                        "                                                                           \n" +
                        "8 - The set of holders of a doctorate degree per establishment and per year \n" +
                        "9 - The set of teachers that changed from one establishment to another one per year \n" +
                        "10 - List of establishments per year                                       \n" +
                        "11 - List of holders of a degree per year                                  \n" +
                        "12 - Personnel leaving the institution and new personnel in the institution per year \n" +
                        "*************************************************************************************\n");
                Console.WriteLine("Choose the option:");
                string op = Console.ReadLine();
                int option = int.Parse(op);

                switch (option)
                {
                    case 1:
                        Console.WriteLine("Please wait, the first statistic is running");
                        for (int i = 0; i < 10; i++)
                        {
                            Peryear(i, "StatisticTeacherPerYear.txt");
                        }
                        break;
                    case 2:
                        Console.WriteLine("Insert the Year you want?");
                        string y1 = Console.ReadLine();
                        int year = int.Parse(y1);
                        Console.WriteLine("Insert the Establishment you want?");
                        string establishment = Console.ReadLine();
                        Console.WriteLine("Please wait, the second statistic is running");
                        PerEstablishmentPeryear(year, establishment, "StatisticTeacherPerEstablishmentPerYear.txt");
                        break;
                    case 3:
                        Console.WriteLine("Insert the Year you want?");
                        string y2 = Console.ReadLine();
                        int year2 = int.Parse(y2);
                        Console.WriteLine("Insert the Degree you want?");
                        string degree = Console.ReadLine();
                        Console.WriteLine("Please wait, the third statistic is running");
                        PerDegreePeryear(year2,degree, "StatisticTeacherPerDegreePerYear.txt");
                        break;
                    case 4:
                        Console.WriteLine("Insert the Year you want?");
                        string y3 = Console.ReadLine();
                        int year3 = int.Parse(y3);
                        Console.WriteLine("Insert the Degree you want?");
                        string degree2 = Console.ReadLine();
                        Console.WriteLine("Insert the Establishment you want?");
                        string establishment2 = Console.ReadLine();
                        Console.WriteLine("Please wait, the fourth statistic is running");
                        PerEstablishmentPerDegreePeryear(year3, degree2, establishment2, "StatisticTeacherPerDegreePerEstablishmentPerYear.txt");
                        break;
                    case 5:
                        Console.WriteLine("Insert the Year you want?");
                        string y4 = Console.ReadLine();
                        int year4 = int.Parse(y4);
                        Console.WriteLine("Insert the Establishment you want?");
                        string establishment3 = Console.ReadLine();
                        Console.WriteLine("Please wait, the fifth statistic is running");
                        DoctoratePerEstablishmentPeryear(year4, establishment3, "StatisticDoctoratePerEstablishmentPerYear.txt");
                        break;
                    case 6:
                        Console.WriteLine("Insert the frist Establishment you want?");
                        string frist = Console.ReadLine();
                        Console.WriteLine("Insert the second Establishment you want?");
                        string second = Console.ReadLine();
                        Console.WriteLine("Please wait, the sixth statistic is running");
                        NumberTeachersMigrateEstablishment("frist", "second", "StatisticNumberTeachersMigrateToEstablishment.txt");
                        break;
                    case 7:
				        //Console.WriteLine("Insert the Year you want?");
                        //string y5 = Console.ReadLine();
                        //int year5 = int.Parse(y5);
                        Console.WriteLine("Insert the Establishment you want?");
                        string establishment5 = Console.ReadLine();
                        Console.WriteLine("Please wait, the seventh statistic is running");
				        for (int i = 0; i < 10; i++)
                        {
                        NumberTeachersPromoteCategoryPerEstablisment(i, establishment5, "StatisticNumberTeachersPromoteNextCategory.txt");
						}
						break;
                    case 8:
                        Console.WriteLine("Insert the Establishment you want?");
                        string establishment4 = Console.ReadLine();
                        Console.WriteLine("Please wait, the octave statistic is running");
                        for (int i = 0; i < 10; i++)
                        {
                            ListDoctoratePerEstablishmentPeryear(i, establishment4, "ListDoctoratePerEstablishmentPerYear.txt");
                        }
                        break;
                    case 9:
                        Console.WriteLine("Insert the frist Establishment you want?");
                        string frist1 = Console.ReadLine();
                        Console.WriteLine("Insert the second Establishment you want?");
                        string second1 = Console.ReadLine();
                        Console.WriteLine("Please wait, the ninth statistic is running");
                        for (int i = 0; i < 10; i++)
                        {
                            ListTeachersChangeEstablishmentPerYear(i, frist1, second1, "ListTeachersChangeEstablishmentPerYear.txt");
                        }
                        break;
                    case 10:
                        Console.WriteLine("Please wait, the tenth statistic is running");
                        for (int i = 0; i < 10; i++)
                        {
                            ListEstablismentPerYear(i, "ListEstablishmentPerYear.txt");
                        }
                        break;
                    case 11:
                        Console.WriteLine("Insert the Degree you want?");
                        string degree3 = Console.ReadLine();
                        Console.WriteLine("Please wait, the eleventh statistic is running");
                        for (int i=0; i < 10; i++)
                        {
                            ListHoldersDegreePerYear(i,degree3, "ListHoldersDegreePerYear.txt");
                        }
                        break;
                    case 12:
                        Console.WriteLine("Insert the Establishment you want?");
                        string establishment6 = Console.ReadLine();
                        Console.WriteLine("Please wait, the twelfth statistic is running");
                        for (int i = 0; i < 9; i++)
                        {
                            ListPersonnelLeaveOrEntry(i, establishment6, "ListPersonnelLeaving.txt", "ListPersonnelEntry.txt");
                        }
                        break;
                    default:
                        Console.WriteLine("Número Não Correcto");
                        break;

                }
				Console.Write("--DONE--  (Press any key)");
                Console.ReadKey();
        }
	}
}





